﻿using System;
using System.Data;

namespace WorkOrder.Business
{
    public class MonthReport
    {
        DbHelper dbhelper = new DbHelper();

        #region 开通客户（家）
        public DataTable getNewCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 开通已完成
        public DataTable getNewCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1 and A.is_back=0 and A.finish_time is not null and (A.order_status='已关闭' or A.order_status='待业务确认') and A.finish_time > '2000-1-1'" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 开通支撑次数
        public DataTable getNewCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=1" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消客户（家）
        public DataTable getCancelCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消已完成
        public DataTable getCancelCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2 and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') and A.finish_time is not null and A.finish_time > '2000-1-1'" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消支撑次数
        public DataTable getCancelCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=2" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整客户（家）
        public DataTable getAdjustCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整已完成
        public DataTable getAdjustCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3 and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') and A.finish_time is not null and A.finish_time > '2000-1-1'" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整支撑次数
        public DataTable getAdjustCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=3" + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        public int[] getPrevMonth(string str)
        {
            int[] tmp = new int[9] { 0, 0, 0, 0, 0, 0, 0, 0, 0 };
            string sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=1 and task_time between " + str;
            tmp[0] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') and task_time between " + str + " and finish_time between " + str;
            tmp[1] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,table_work_order B where A.order_id=B.order_id and B.order_type_id=1 and request_time between " + str;
            tmp[2] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) + tmp[0];
            sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=3 and task_time between " + str;
            tmp[3] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=3 and (order_status='已关闭' or order_status='待业务确认') and task_time between " + str + " and finish_time between " + str;
            tmp[4] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,table_work_order B where A.order_id=B.order_id and B.order_type_id=3 and request_time between " + str;
            tmp[5] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) + tmp[3];
            sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=2 and task_time between " + str;
            tmp[6] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(order_id as varchar(40))) from table_work_order where order_type_id=2 and (order_status='已关闭' or order_status='待业务确认') and task_time between " + str + " and finish_time between " + str;
            tmp[7] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr));
            sqlstr = "select count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,table_work_order B where A.order_id=B.order_id and B.order_type_id=2 and request_time between " + str;
            tmp[8] = Convert.ToInt32(dbhelper.ExecuteScalar(sqlstr)) + tmp[6];
            return tmp;
        }

        #region 新增退单
        public DataTable getNewReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消退单
        public DataTable getCancelReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整退单
        public DataTable getAdjustReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return dbhelper.Query("", sqlstr);
        }
        #endregion
    }
}
